﻿using CKS.Schedule;
using FreeSql;
using Infrastructure;
using MailKit.Search;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.SignalR;
using Microsoft.Extensions.DependencyInjection;
using SqlSugar;
using SqlSugar.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using ZR.Infrastructure;
using ZR.Model.Models.ProductionControl;
using ZR.ServiceCore.Signalr;

namespace CKS.PLC.Core.CronJob {
    /// <summary>
    /// 散热片大屏推送示例
    /// </summary>
    [Schedule]
    public class PushSRPDataJob : CronJobBase {

        private const string _LineCode = "Radiator";
        private readonly IHubContext<MessageHub> _signalHub;
        private readonly IFreeSql<MesDB> _fsql;

        #region 生产计划
        /// <summary>
        /// 开始生产时间
        /// </summary>
        private DateTime _startProductionTime = DateTime.Today.AddHours(9);

        /// <summary>
        /// 结束生产时间
        /// </summary>
        //private DateTime _endProductionTime = new DateTime(1970, 1, 1, 9, 0, 0);


        /// <summary>
        /// 计划生产时间(秒) 
        ///<summary>
        private static int _plannedProductionTime = (8 * 60 * 60);
        private static int _restTime = 60 * 60; //休息时间1小时
        private static int _uncontrollableTime = 20 * 60; //不可抗力时间20分钟
        private int _planRunTime = (_plannedProductionTime - _restTime - _uncontrollableTime);//计算计划时间
        #endregion

        public PushSRPDataJob() : base("散热片产线推送数据服务", "0/1 * * * * ?") {
            _signalHub = InternalApp.ServiceProvider.GetService<IHubContext<MessageHub>>();
            _fsql = InternalApp.ServiceProvider.GetService<IFreeSql<MesDB>>();
        }

        protected override async Task ExecuteAsync(CancellationToken cancellationToken) {
            /*
                先组织所有的数据，再将数据一次推送给大屏客户端
                本示例数据均为展示需要。 实际项目可能涉及到查询数据库进行统计
            */
            if (_signalHub == null)
                return;

            string startDate = $"{DateTime.Now.ToShortDateString()} 00:00:00";//.AddDays(-30)
            string endDate = $"{DateTime.Now.ToShortDateString()} 23:59:59";

            // 获取散热片产线的所有工站信息
            var plcList = _fsql.Select<PlcConfig, CksPlcBlockDb>()
                 .LeftJoin((a, b) => a.Id == b.PlcId)
                 .Where((a, b) => a.ProductionLineCode == _LineCode)
                 .OrderBy((a, b) => b.Sequence)
                 .ToList((a, b) => new { a.Id, a.ProductionLineCode, a.WorkstationCode, b.BlockDb, b.Remark });

            #region 获取最新的12条报警信息
            //var queryAlamsDt = _fsql.Select<CksAlarmRecord, CksAlarmMessageConfig, PlcConfig>()
            //    .LeftJoin((a, b, c) => a.PlcID == b.PlcID && a.BlockAddr == b.BlockAddr && a.AlarmAddr == b.AlarmAddr && a.AlarmBitIndex == b.AlarmBitIndex)
            //    .LeftJoin((a, b, c) => c.Id == a.PlcID)
            //    .Where((a, b, c) => a.EndTime == null && a.StartTime >= Convert.ToDateTime(startDate) && a.StartTime <= Convert.ToDateTime(endDate) && c.ProductionLineCode == _LineCode)
            //    .OrderByDescending((a, b, c) => a.StartTime)
            //    .Take(12)
            //    .ToList((a, b, c) => new {
            //        a.Id,
            //        a.PlcID,
            //        c.ProductionLineCode,
            //        c.WorkstationCode,
            //        b.MessageCn,
            //        a.StartTime
            //    });

            // 获取最新的12条报警信息
            var alamsDt = _fsql.Ado.ExecuteDataTable(
                    @$"select a.Id,a.PlcID,c.production_line_code,c.workstation_code,b.MessageCn,a.StartTime
                    from cks_alarm_rec a
                    left join cks_alarm_message_config b on a.PlcID = b.PlcID and a.BlockAddr = b.BlockAddr and a.AlarmAddr = b.AlarmAddr and a.AlarmBitIndex = b.AlarmBitIndex
                    left join plc_config c on c.id = a.PlcID
                    where a.EndTime is null and a.StartTime BETWEEN '{startDate}' and '{endDate}' and c.production_line_code = '{_LineCode}'
                    order by a.StartTime desc 
                    limit 12");
            #endregion

            #region 每个站最新的加工记录
            //获取每个工站最新的加工记录 && (t4.IsCycleTime.Value || t4.IsCycleTime == null)
            //var queryWorkStationInfoDt = _fsql.Select<CksWorkRecord, CksCollected, CksCollectedDetails, CksVirtualColumn, PlcConfig>()
            //    .LeftJoin((t1, t2, t3, t4, t5) => t2.WorkRecId == t1.Id)
            //    .LeftJoin((t1, t2, t3, t4, t5) => t2.BatchSuffix == t3.BatchSuffix)
            //    .LeftJoin((t1, t2, t3, t4, t5) => t4.Id == t3.ColumnId)
            //    .LeftJoin((t1, t2, t3, t4, t5) => t5.Id == t1.PlcID)
            //    .Where((t1, t2, t3, t4, t5) => t1.CreatedTime >= Convert.ToDateTime(startDate) && t1.CreatedTime <= Convert.ToDateTime(endDate) && t5.ProductionLineCode == _LineCode)
            //    .OrderByDescending((t1, t2, t3, t4, t5) => t1.CreatedTime)
            //    .ToList((t1, t2, t3, t4, t5) => new {
            //        t1.Id,
            //        t1.PlcID,
            //        t1.CreatedTime,
            //        SN = t1.PrimaryCode,
            //        EnterTime = t1.MoveInTime,
            //        ExitTime = t1.MoveOutTime,
            //        Result = t2.Result.ToString() ?? "--",
            //        ProccessTime = t3.Content ?? "--",
            //        rn = SqlExt.RowNumber().Over().PartitionBy(new { t1.PlcID , t1.ReadDB }).OrderByDescending(t1.CreatedTime).ToValue()
            //    })
            //    .Where(x => x.rn == 1)
            //    .OrderBy(x => x.CreatedTime)
            //    .ToList();

            // 获取每个工站最新的加工记录  and (t4.IsCycleTime = 1 OR t4.IsCycleTime IS NULL) 
            var workStationInfoDt = _fsql.Ado.ExecuteDataTable(
                @$"WITH RankedRecords AS (
                    SELECT t1.ID,t1.PlcID,t1.ReadDB,t1.CreatedTime,t1.PrimaryCode AS SN,t1.MoveInTime AS EnterTime,
                        t1.MoveOutTime AS ExitTime,t2.Result AS Result,t2.CycleTime AS ProccessTime,
                        ROW_NUMBER() OVER (PARTITION BY t1.plcid,t1.readdb ORDER BY t1.CreatedTime DESC) AS rn
                    FROM cks_work_rec t1
                    LEFT JOIN cks_collected t2 ON t2.workrecid = t1.id
					LEFT JOIN plc_config t3 on t3.id = t1.plcid
                    WHERE t1.CreatedTime BETWEEN '{startDate}' and '{endDate}' and t3.production_line_code = '{_LineCode}'
                )
                SELECT ID,PlcID,CreatedTime,SN,EnterTime,ExitTime,Result,ProccessTime,ReadDB
                FROM RankedRecords
                WHERE rn = 1
                ORDER BY CreatedTime ASC;");
            #endregion

            #region 每个站的OEE
            // 获取所有工站的的OEE总合
            var deviceRunRecordList = _fsql.Select<CksDeviceRunRecord, PlcConfig>()
                 .Where((a, b) => a.CreateTime >= Convert.ToDateTime(startDate) && a.CreateTime <= Convert.ToDateTime(endDate) && b.ProductionLineCode == _LineCode)
                 .ToList((a, b) => a);

            // 总运行时间（秒）
            var totalRunTime = (DateTime.Now - _startProductionTime).TotalSeconds;
            // 总故障时间（秒）
            var totalFaultTime = DownTimeCalculator.CalculateTotalDownTime(deviceRunRecordList);

            // 计算每个工站的OEE
            var listOee = new List<Tuple<int, int, double>>();
            var listResult = new List<Tuple<int, int, int, int>>();
            foreach (var item in plcList) {
                //计算当前plc故障时间
                var curFaultTime = DownTimeCalculator.CalculateTotalDownTime(deviceRunRecordList.Where(r => r.PlcId == item.Id).ToList());
                //计算稼动率【线体实际运行时间 / 线体计划运行时间】
                var availability = (_planRunTime - curFaultTime) / _planRunTime;

                //计算性能效率【（线体实际总产出 / 线体实际运行时间） / 线体理论节拍】或【线体实际总产出 / （线体实际运行时间 × 线体理论节拍）】
                var totalCountList = _fsql.Select<CksCollected>().Where(o => o.PlcId == item.Id && o.CreatedTime >= Convert.ToDateTime(startDate) && o.CreatedTime <= Convert.ToDateTime(endDate)).ToList();
                var totalCount = totalCountList.Count();
                var performance = totalCount == 0 ? 0 : (totalCount) / (totalRunTime / 60);//假设理想状态下每60秒一个产品

                //计算质量效率
                var okCount = totalCountList.Where(o => o.Result == 1).Count();
                var ngCount = totalCountList.Where(o => o.Result == 2).Count();
                var quality = totalCount == 0 ? 0 : (double)okCount / totalCount;

                //计算OEE(稼动率*性能效率*质量效率)
                var oee = availability * performance * quality;
                listOee.Add(new Tuple<int, int, double>(item.Id, item.BlockDb, oee));

                listResult.Add(new Tuple<int, int, int, int>(item.Id, (int)totalCount, (int)okCount, (int)ngCount));
            }

            // 汇总工站OEE，平均值
            var totalAvgOee = listOee.Count == 0 ? 0 : listOee.Select(o => o.Item3).Average();
            // 汇总工站OEE，几何平均值
            //var totalAvgOee = listOee.Count == 0 ? 0 : Math.Pow(listOee.Select(o => o.Item3).Aggregate((a, b) => a * b), 1.0 / listOee.Count);
            #endregion


            //构建推送信息
            var response = new PushResponseDto();

            //构建报警信息
            response.Alarms = new List<AlarmsDto>();
            if (alamsDt != null && alamsDt.Rows.Count > 0) {

                for (int i = 0; i < alamsDt.Rows.Count; i++) {
                    AlarmsDto alarmsDto = new AlarmsDto {
                        Id = alamsDt.Rows[i]["Id"].ObjToInt(),
                        Content = $"{alamsDt.Rows[i]["production_line_code"]}-{alamsDt.Rows[i]["workstation_code"]}-{alamsDt.Rows[i]["MessageCn"]}",
                        AlarmTime = Convert.ToDateTime(alamsDt.Rows[i]["StartTime"]).ToString("yyyy-MM-dd HH:mm:ss")
                    };
                    response.Alarms.Add(alarmsDto);
                }
            }

            //构建每个工站的工站详情
            response.DemoStationData = new List<StationDataDto>();
            foreach (var plcItem in plcList) {
                // 查找该工站最新的加工记录
                var workRec = workStationInfoDt.Select($"PlcID={plcItem.Id} and readDb={plcItem.BlockDb}").FirstOrDefault();
                StationDataDto data = new StationDataDto();
                data.Name = $"{plcItem.ProductionLineCode}-{plcItem.Remark}";
                if (workRec != null) {
                    data.EnterTime = workRec["EnterTime"] is not DBNull ? Convert.ToDateTime(workRec["EnterTime"]).ToString("yyyy-MM-dd HH:mm:ss") : "-";
                    data.ExitTime = workRec["ExitTime"] is not DBNull ? Convert.ToDateTime(workRec["ExitTime"]).ToString("yyyy-MM-dd HH:mm:ss") : "-";
                    data.ProccessTime = workRec["ProccessTime"] is not DBNull ? workRec["ProccessTime"].ToString() + "s" : "-";
                    data.SN = workRec["SN"].ToString();
                    data.Result = workRec["Result"].ToString() == "1" ? "OK" : workRec["Result"].ToString() == "2" ? "NG" : "-";
                } else {
                    data.EnterTime = "--";
                    data.ExitTime = "--";
                    data.ProccessTime = "--";
                    data.SN = "--";
                    data.Result = "--";
                }
                response.DemoStationData.Add(data);
            }

            //构建OEE数据
            OEEDto oEEDto = new OEEDto();
            {
                oEEDto.FailureTime = totalFaultTime > 0 ? TimeSpan.FromSeconds(totalFaultTime).ToString(@"hh\:mm\:ss") : "00:00:00";
                oEEDto.RunTime = totalRunTime > 0 ? TimeSpan.FromSeconds((int)totalRunTime).ToString(@"hh\:mm\:ss") : "00:00:00";
                oEEDto.OperatingRate = $"{totalAvgOee:P2}";
                oEEDto.TotalCount = listResult.LastOrDefault().Item2;
                oEEDto.OkCount = listResult.LastOrDefault().Item3;
                oEEDto.NgCount = listResult.LastOrDefault().Item4;
            }

            response.OEE = oEEDto;

            await _signalHub.Clients.Clients(MessageHub.SrpLineBigScreenUsers)
                .SendAsync("SRPLineBigScreenReceive", response);

            await Task.CompletedTask;
        }
    }

}